The present invention relates generally to databases and database management systems. More specifically, the present invention relates to systems for dynamically retrieving information stored in a relational database using a join query and transforming the same into a form suitable for storage in a network of heterogeneous object-oriented data structures manipulable by an object-oriented application.
A. Relational Database Management Systems Generally
Relational databases store data tables. Each table includes data arranged in a plurality of records, with each record represented as a plurality of rows in the table. Each record/row generally includes a primary key data element and one or more data elements defining attributes of the keyed item. For example, in an EMPLOYEE table, the primary key might be the employee number of an employee. Attributes of the employee might include the employee's first name, last name, and the employee's status as either hourly or salaried. A record might also include one or more foreign key data elements which indicate how that record in the table is related to another record in a separate table. For example, in the EMPLOYEE table, a DEPARTMENT foreign key would indicate the relationship of an employee to a record in a DEPARTMENT table.
A relational database management system (RDBMS) constructs views of the data in a relational database by issuing a Structured Query Language (SQL) join query to select data from one or more tables in the relational database. The results of the join query are then loaded into a single results table.
The following example illustrates this process. A certain relational database stores information relating the departments of a company in one table, and information relating to the employees of the company in another table. To view the attributes of all of the employees in the engineering department, for example, one issues an SQL "join" query to select data from the "DEPARTMENT" table and the "EMPLOYEE" table. The resulting data is loaded into a single results table such that each row of the results table includes attributes from both the DEPARTMENT table and the EMPLOYEE table.
B. Object-Oriented Data Structure and Applications
Object-oriented applications organize routines together with the data operated upon by the routines into discrete units referred to as "objects." The objects then exist in an object-oriented data structure manipulable by the object-oriented application. Each object in the object-oriented data structure represents an instance of a "class." A class abstractly defines the characteristics of each object in the class. For example, in a STUDENT class, objects may be defined as having a student ID, a name, a major, and a GPA. An object of the STUDENT class might be a student named Ed Jones, having a student ID of 123-45-6789, a Computer Science Major, with a GPA of 3.65.
Sub-classes may also be defined for a given class. For example, for the STUDENT class described above, a sub-class of GRADUATE STUDENT may be defined which has all of the characteristics of a STUDENT but which also has certain characteristics unique to a GRADUATE STUDENT (e.g., a graduate advisor, a graduate thesis topic). An object of a sub-class then "inherits" the characteristics of the class from which it is derived (e.g., on object of the GRADUATE STUDENT class "inherits" the fact that it has an ID, a name, a major and a GPA from the STUDENT class).
The relationship of one object to another may be defined implicitly or explicitly. An implicit relationship between objects occurs through class inheritance as described above. An explicit relationship occurs between objects when an explicit link is defined via a pointer, a linked list, etc. to indicate the relationship of one object to another object or objects in the object-oriented data structure.
Object-oriented applications (e.g., an object-oriented database management system) have the facility to navigate quickly and efficiently through data that is organized as a network of heterogeneous inter-related objects. Hence, as compared to RDBMS's, object-oriented applications are preferable for performing certain data navigation operations that are more cumbersome for a traditional RDBMS. A simple example is the bill of materials problem where, for example, one wants to navigate all of the components which make up a lawnmower in order to determine the total weight of the lawnmower. One can easily represent the lawnmower as a network of inter-related objects (i.e., a plurality of parts related to each other in a pre-determined way). Hence, once the desired part is represented in an object-oriented data structure, the total weight is calculated easily by navigating through the objects and summing their respective weights. In comparison, calculating the weight of the lawnmower using a traditional RDBMS alone potentially requires hundreds of join queries--a cumbersome and tedious exercise. For a more detailed explication of the bill of materials problems, the reader is referred to the text File Organization for Database Design by George Wiederhold (McGraw-Hill, 1987), which is hereby incorporated by reference.
C. Transforming Relational Database Information into Object-Oriented Form
Because of the large volume of data which is already stored in traditional relational database form, systems for transforming relational-database data into a form suitable for manipulation by an object-oriented application have been developed. One such system is disclosed in U.S. Pat. No. 5,615,362 entitled METHOD AND APPARATUS FOR MANAGING RELATIONAL DATA IN AN OBJECT CACHE, which is hereby incorporated by reference for all purposes.
The above-identified patent describes a method and apparatus for managing a single cohesive object-oriented data structure (e.g., an object cache) with a plurality of inter-related objects representing information retrieved from a relational database. The object cache is suitable for manipulation by an object-oriented application program.
When the object-oriented application attempts to perform an operation on information which is not currently in the object-oriented data structure (i.e., the object does not exist), the apparatus and method described in U.S. Pat. No. 5,615,362 composes one or more relational database queries to retrieve the required information from the relational database. Each query addresses a single table or view, and the results of each query are mapped into a single class of objects.
FIG. 1 illustrates the prior art method for transforming one or more results tables into a network of heterogeneous inter-related objects in an object-oriented data structure. Relational database 99 includes two tables, TABLE A and TABLE B. TABLE A includes three records, A1, A2, and A3, and TABLE B includes at least four records, B1, B2, B3 and B4. The records of TABLE B also include foreign key information indicating how the records in TABLE B correspond to the records in TABLE A. The described method composes four separate queries to produce the results tables 81, 83, 85, 87 shown in FIG. 1.
The prior art method composes a first query to retrieve all of the records of TABLE A. An exemplary SQL query is as follow:
SELECT * FROM TableA PA1 SELECT * FROM TableB WHERE fkB=`A1` PA1 SELECT * FROM TableB WHERE fkB=`A2` PA1 SELECT * FROM TableB WHERE fkB=`A3`
Results table 81 is the product of the above query. The method then transforms the three rows of results table 81, R1, R2, and R3, into the three objects, A1, A2 and A3, respectively, of CLASS A in object-oriented data structure 79.
The prior art method composes a second query to retrieve all of the records of TABLE B where the foreign key equals "A1." An exemplary SQL query is as follows:
In this case, two records satisfy the query thereby producing results table 83. The method transforms the two rows, R1 and R2, of results table 83 into two objects, B1 and B2, of CLASS B as shown. In addition, the method uses the foreign key information in each row of results table 83 to define the relationships between objects B1 and B3 of CLASS B and object A1 of CLASS A.
The prior art method composes a third query to retrieve all of the records of TABLE B where the foreign key equals "A2." An exemplary SQL query is as follows:
In this case, one record satisfies the query thereby producing results table 85. The method then transforms the single row R1 of results table 85 into object B2 of CLASS B as shown. In addition, the method uses the foreign key information in R1 of results table 85 to define the relationship between object B2 and object A2.
The prior art method composes a fourth query that retrieves all of the records of TABLE B where the foreign key equals A3. An exemplary SQL query is as follows:
In this case, one row of TABLE B satisfies the query thereby producing results table 87. The method transforms row R1 of results table 87 into object B4 of CLASS B as shown. In addition, the method uses the foreign key information in R1 of results table 87 to define the relationship between object B4 of CLASS B and object A3 of CLASS A.
The foregoing method is limited in that a single row of a results table can be transformed into a single object only. Because of this limitation, the described method is restricted to composing only simple queries which operate on only a single relational database table at a time. This approach is does not take full advantage of the power of current relational database management systems which can form complex queries which operate on multiple tables of the relational database at a time. It would be desirable, therefore, to provide a method and system for transforming relational database information into an object-oriented data structure which can transform the results of more complex relational database queries into an object-oriented form. Such a method and system would provide greater efficiency in populating an object-oriented data structure from data stored in a relational database.
D. Prior Art References
The following publications are believed to relate to the general background of the present invention and are presented herein to satisfy the duty of disclosure. It is not believed that any one is specifically relevant to the claimed invention.
M. R. Blaha, W. J. Premerlani, and J. E. Rumbaugh, "Relational Database Design Using an Object-Oriented Approach," Communications of the ACM, Vol. 31, No. 4, April 1988, pp. 414-427.
Barsalou, T., Siambela, N., Keller, A. M., & Wiederhold, G., Updating Relational Databases through Object-Based Views, ACM SIGMOD Proceedings, Denver, Colo., May 1991.
R. G. G. Cattell, Object Data Management: Object-Oriented and Extended Relational Database Systems, Addison-Wesley Publishing Company, Reading, Mass., 1991.
J. Duhl and C. Damon, "A Performance Comparison of Object and Relational Databases Using the Sun Benchmark," OOPSLA '88 Conference Proceedings, special issue of SIGPLAN Notices, Vol. 23, No. 11, November 1988, pp. 153-163.
J. A. Goguen and J. Meseguer, "Unifying Functional, Object-Oriented and Relational Programming With Logical Semantics," Research Directions in Object-Oriented Programming, B. Shriver and P. Wegner, Editors, The MIT Press, Cambridge, Mass., 1987, pp. 417-477.
Gray, J., & Reuter, A., Transaction Processing: Concepts and Techniques, Morgan Kaufman, San Mateo, Calif., 1993.
Keller, A. M., Updating relational databases through views, Ph.D. Dissertation, Department of Computer Science, Stanford University, February, 1985.
Keller, A. M., Agarwal, S. & Jensen, R., Enabling The Integration of Object Applications With Relational Databases, ACM SIGMOD Proceedings, 1993.
Keller, A. M., & Basu, J., A Predicate-based Caching Scheme for Client-Server Database Architectures, VLDB Journal, 1995.
Nag, B. & Zhao, Y., Implementing the 007 Benchmark on Persistence, Master's Thesis, Computer Sciences Department, University of Wisconsin-Madison.
W. J. Premerlani, M. R. Blaha, J. E. Rumbaugh, and T. A. Varwig, "An Object-Oriented Relational DBMS," Communications of the ACM, Vol. 33, No. 11, November 1990, pp. 99-109.
Wiederhold, G., Mediators in the Architecture of Future Information Systems, IEEE Computer, March 1992.